package com.multicampus.minishop.services.product.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.multicampus.minishop.common.SearchVO;
import com.multicampus.minishop.product.vo.ProductVO;
import com.multicampus.minishop.util.DBUtil;

public class ProductDAO {

	public ProductVO findProduct(int prodNo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "select * from product where prod_no=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, prodNo);

			ResultSet rs = stmt.executeQuery();

			ProductVO vo = null;
			while (rs.next()) {

				vo = new ProductVO();
				vo.setFileName(rs.getString("image_file"));
				vo.setManuDate(rs.getString("manufacture_day"));
				vo.setPrice(rs.getInt("price"));
				vo.setProdDetail(rs.getString("prod_detail"));
				vo.setProdName(rs.getString("prod_name"));
				vo.setProdNo(rs.getInt("prod_no"));
				vo.setRegDate(rs.getDate("REG_DATE"));
			}

			con.close();
			return vo;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}

	
		
	}		
		
	public ProductVO insertProduct(ProductVO vo) throws Exception{
		try{
			
			Connection con = DBUtil.getConnection();
			String sql="insert into product values(MINISHOP_PROD.nextval,?,?,?,?,?,sysdate)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vo.getProdName());
			pstmt.setString(2, vo.getProdDetail());
			pstmt.setString(3, vo.getManuDate());
			pstmt.setInt(4, vo.getPrice());
			pstmt.setString(5, vo.getFileName());
			pstmt.executeUpdate();
			
			sql="select prod_no from product where rownum=1 order by prod_no desc";
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			int prodNo = 0;
			while (rs.next()) {
				prodNo =  rs.getInt("prod_no");
			}
			con.close();
			
			return findProduct(prodNo);
				
			
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}
	}
	
	
	public void updateProduct(ProductVO vo) throws Exception {
		try {
			Connection con = DBUtil.getConnection();

			String sql = "update product set prod_name=?,prod_detail=?,manufacture_day=?,price=?, IMAGE_FILE=? where prod_no=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, vo.getProdName());
			stmt.setString(2, vo.getProdDetail());
			stmt.setString(3, vo.getManuDate());
			stmt.setInt(4, vo.getPrice());
			stmt.setString(5, vo.getFileName());
			stmt.setInt(6, vo.getProdNo());
			stmt.executeUpdate();

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}

	}
		
	public HashMap getProductList(SearchVO searchVO) throws Exception {
		HashMap map = null;
		try {
			Connection con = DBUtil.getConnection();
			System.out.println(searchVO.getSearchKeyword());
//			where p.prod_no = t.prod_no
//			  and nvl(t.tran_status_code,'t04') <> 't02'
//			  and nvl(t.tran_status_code,'t04') <> 't03'
//			  and nvl(t.tran_status_code,'t04') <> 't01'
			String sql = "select * from product p , transaction t where p.prod_no = t.prod_no (+) ";
			sql += "and nvl(t.tran_status_code,'t04') <> 't01'";
			sql += "and nvl(t.tran_status_code,'t04') <> 't02'";
			sql += "and nvl(t.tran_status_code,'t04') <> 't03'";
			if (searchVO.getSearchCondition() != null) {
				if (searchVO.getSearchCondition().equals("0")) {
					sql += " and p.prod_name like '%" + searchVO.getSearchKeyword()
							+ "%'";
				} else if (searchVO.getSearchCondition().equals("1")) {
					sql += " and p.price='" + searchVO.getSearchKeyword()
							+ "'";
				}
//				else if (searchVO.getSearchCondition().equals("2")) {
//					sql += " and to_Char(p.reg_date)='" + searchVO.getSearchKeyword()
//							+ "'";
//				}
			}
			sql += " order by p.prod_no desc";
			System.out.println(sql);

			PreparedStatement stmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery();

			rs.last();
			int total = rs.getRow();

			map = new HashMap();
			map.put("count", new Integer(total));



			rs.absolute(searchVO.getPage() * searchVO.getPageUnit() - 2);

			ArrayList list = new ArrayList();
			if (total > 0) {
				for (int i = 0; i < searchVO.getPageUnit(); i++) {

					ProductVO vo = new ProductVO();
					vo.setProdNo(rs.getInt("prod_no"));
					vo.setProdName(rs.getString("prod_name"));
					vo.setProdDetail(rs.getString("prod_detail"));
					vo.setManuDate(rs.getString("manufacture_day"));
					vo.setPrice(rs.getInt("price"));
					vo.setFileName(rs.getString("image_file"));
					vo.setRegDate(rs.getDate("REG_DATE"));

					list.add(vo);
					if (!rs.next())
						break;
				}
			}
			map.put("list", list);

			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return map;
	}
	
	
	
	
	
	
	

}
